package com.muk.meis.model.service;

import java.io.StringWriter;
import java.util.Iterator;
import java.util.List;

import javax.persistence.EntityTransaction;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Text;

import com.muk.meis.model.bean.OpecSchool;

public class OpecSchoolService extends BaseMEISService {

	public List getByPercode(String percode) {
		List lst = em.createQuery(
				"select a.opecName, b.opecPoint, a.opecId "+
				" from OpecSetup a left outer join a.opecSchool b "+
				" where b.percodeCode = '"+percode+"'"+
				"       or b.percodeCode is null"+
				" order by 3 ").getResultList();
		if(lst.size() > 0) return lst;
		List lstOPEC = em.createQuery(
				"select a.opecName, 0, a.opecId "+
				" from OpecSetup a "+
				" order by 3 ").getResultList();		
		return lstOPEC;
	}
	
	public void clearToNew(String percode) {
		EntityTransaction t = em.getTransaction();
		t.begin();
		List<OpecSchool> lst = em.createQuery(
				"select e from OpecSchool e where e.percodeCode = '" + percode
						+ "'", OpecSchool.class).getResultList();
		for (OpecSchool opecSchool : lst) {
			em.remove(opecSchool);
		}
		t.commit();
	}
	
	public void update(List<OpecSchool> opecSchoolLst) {
		EntityTransaction t = em.getTransaction();
		t.begin();
		for (OpecSchool opecSchool : opecSchoolLst) {
			em.persist(opecSchool);			
		}
		t.commit();
	}
	
	public String getDataGraph2(String percode){
//		 chart_data: "1;20\n2;15\n3;12\n4;18\n5;11\n6;19\n7;25\n8;20\n9;15\n10;12\n11;18\n12;11\n13;19\n14;25\n15;12\n16;20\n17;15\n18;17"
		List lst = getByPercode(percode);
		int couter = 1;
		String ret = "";
		for (Iterator iterator = lst.iterator(); iterator.hasNext();) {
			Object[] object = (Object[]) iterator.next();
			ret+="\n"+couter+";"+object[1];
			couter++;
		}
		ret = ret.replaceFirst("\n", "");
//		return ret;
		return "<chart><axes><axis xid='0'>Sales</axis><axis xid='1'>Development</axis><axis xid='2'>Marketing</axis><axis xid='3'>Customer Support</axis><axis xid='4'>Administration</axis><axis xid='5'>Information technology</axis></axes><graphs><graph gid='0'><value xid='0'>20</value><value xid='1'>15</value><value xid='2'>12</value><value xid='3'>18</value><value xid='4'>11</value><value xid='5'>19</value></graph><graph gid='1'><value xid='0'>18</value><value xid='1'>13</value><value xid='2'>16</value><value xid='3'>15</value><value xid='4'>5</value><value xid='5'>13</value></graph></graphs></chart>";
	}
	
	public String getDataGraph(String percode) {
		try {
			// ///////////////////////////
			// Creating an empty XML Document

			// We need a Document
			DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
			DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
			Document doc = docBuilder.newDocument();

			// //////////////////////
			// Creating the XML tree

			// create the root element and add it to the document
			Element root = doc.createElement("chart");
			doc.appendChild(root);

			Element axes = doc.createElement("axes");
			Element graphs = doc.createElement("graphs");
			Element graph = doc.createElement("graph");
			graph.setAttribute("gid", "0");
			List<Object[]> lst = getByPercode(percode);
			int couter = 0;
			int couterExternal = 1;
			for (Object[] obj : lst) {
				Element childAxes = doc.createElement("axis");
				Element childGraphs = doc.createElement("value");
				
				childAxes.setAttribute("xid", couter+"");
				childGraphs.setAttribute("xid", couter+"");
				Text textAxes = doc.createTextNode("มาตรที่"+(couterExternal++));
				Text textGraphs = doc.createTextNode(obj[1].toString());
				
				childAxes.appendChild(textAxes);
				axes.appendChild(childAxes);
				
				childGraphs.appendChild(textGraphs);
				graph.appendChild(childGraphs);
				couter++;
			}
			graphs.appendChild(graph);
			root.appendChild(axes);
			root.appendChild(graphs);

			// ///////////////
			// Output the XML

			// set up a transformer
			TransformerFactory transfac = TransformerFactory.newInstance();
			Transformer trans = transfac.newTransformer();

			// create string from xml tree
			StringWriter sw = new StringWriter();
			StreamResult result = new StreamResult(sw);
			DOMSource source = new DOMSource(doc);
			trans.transform(source, result);
			String xmlString = sw.toString();

			return xmlString.substring(xmlString.indexOf("?>") + 2).replaceAll(
					"\"", "'");
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}
	
	public static void main(String[] args) {
		OpecSchoolService service = new OpecSchoolService();
		List<Object[]> lst = service.getByPercode("730063");
		for(Object [] obj:lst){
			System.out.println(obj[2]);
		}
//		System.out.println(service.getByPercode("730062"));
	}
	
	public static void main2(String[] args) {
        try {
            /////////////////////////////
            //Creating an empty XML Document

            //We need a Document
            DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
            Document doc = docBuilder.newDocument();

            ////////////////////////
            //Creating the XML tree

            //create the root element and add it to the document
            Element root = doc.createElement("pie");
            doc.appendChild(root);

            //create child element, add an attribute, and add to root
            Element child = doc.createElement("child");
            child.setAttribute("title", "What is Christmas?");
            root.appendChild(child);

            //add a text element to the child
            Text text = doc.createTextNode("10");
            child.appendChild(text);

            /////////////////
            //Output the XML

            //set up a transformer
            TransformerFactory transfac = TransformerFactory.newInstance();
            Transformer trans = transfac.newTransformer();
            trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
            trans.setOutputProperty(OutputKeys.INDENT, "yes");

            //create string from xml tree
            StringWriter sw = new StringWriter();
            StreamResult result = new StreamResult(sw);
            DOMSource source = new DOMSource(doc);
            trans.transform(source, result);
            String xmlString = sw.toString();

            //print xml
            System.out.println(xmlString);

        } catch (Exception e) {
            System.out.println(e);
        }

	}
}
